package com.flowplan.todo.repository;

import com.flowplan.entity.Todo;
import com.flowplan.entity.enums.TodoStatus;
import com.flowplan.entity.enums.TodoType;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 待办Repository接口
 * 
 * @author FlowPlan Team
 */
@Repository
public interface TodoRepository extends JpaRepository<Todo, Long> {
    
    /**
     * 根据项目ID查找待办列表
     */
    List<Todo> findByProjectIdOrderByCreateTimeDesc(Long projectId);
    
    /**
     * 根据负责人ID查找待办列表
     */
    List<Todo> findByAssigneeIdOrderByCreateTimeDesc(Long assigneeId);
    
    /**
     * 根据报告人ID查找待办列表
     */
    List<Todo> findByReporterIdOrderByCreateTimeDesc(Long reporterId);
    
    /**
     * 根据状态查找待办列表
     */
    List<Todo> findByStatusOrderByCreateTimeDesc(TodoStatus status);
    
    /**
     * 根据类型查找待办列表
     */
    List<Todo> findByTodoTypeOrderByCreateTimeDesc(TodoType todoType);
    
    /**
     * 分页查询待办列表
     */
    Page<Todo> findAllByOrderByCreateTimeDesc(Pageable pageable);
    
    /**
     * 根据条件分页查询待办
     */
    @Query("SELECT t FROM Todo t WHERE " +
           "(:projectId IS NULL OR t.projectId = :projectId) AND " +
           "(:status IS NULL OR t.status = :status) AND " +
           "(:assigneeId IS NULL OR t.assigneeId = :assigneeId) AND " +
           "(:todoType IS NULL OR t.todoType = :todoType) AND " +
           "(:keyword IS NULL OR t.title LIKE %:keyword% OR t.description LIKE %:keyword%) " +
           "ORDER BY t.createTime DESC")
    Page<Todo> findTodosWithConditions(
            @Param("projectId") Long projectId,
            @Param("status") TodoStatus status,
            @Param("assigneeId") Long assigneeId,
            @Param("todoType") TodoType todoType,
            @Param("keyword") String keyword,
            Pageable pageable);
    
    /**
     * 查找即将到期的待办（24小时内）
     */
    @Query("SELECT t FROM Todo t WHERE " +
           "t.dueDate IS NOT NULL AND " +
           "t.dueDate BETWEEN :now AND :deadlineLimit AND " +
           "t.status NOT IN ('DONE', 'BLOCKED')")
    List<Todo> findTodosNearDeadline(
            @Param("now") LocalDateTime now,
            @Param("deadlineLimit") LocalDateTime deadlineLimit);
    
    /**
     * 查找已过期的待办
     */
    @Query("SELECT t FROM Todo t WHERE " +
           "t.dueDate IS NOT NULL AND " +
           "t.dueDate < :now AND " +
           "t.status NOT IN ('DONE')")
    List<Todo> findOverdueTodos(@Param("now") LocalDateTime now);
    
    /**
     * 统计项目下各状态的待办数量
     */
    @Query("SELECT t.status as status, COUNT(t) as count FROM Todo t WHERE t.projectId = :projectId GROUP BY t.status")
    List<Object[]> countTodosByStatusAndProject(@Param("projectId") Long projectId);
    
    /**
     * 统计用户分配的待办数量
     */
    @Query("SELECT COUNT(t) FROM Todo t WHERE t.assigneeId = :assigneeId AND t.status != 'DONE'")
    Long countActiveByAssigneeId(@Param("assigneeId") Long assigneeId);
    
    /**
     * 统计用户创建的待办数量
     */
    @Query("SELECT COUNT(t) FROM Todo t WHERE t.reporterId = :reporterId")
    Long countByReporterId(@Param("reporterId") Long reporterId);
    
    /**
     * 查找用户相关的所有待办（分配给用户或用户创建的）
     */
    @Query("SELECT t FROM Todo t WHERE t.assigneeId = :userId OR t.reporterId = :userId ORDER BY t.createTime DESC")
    List<Todo> findTodosByUser(@Param("userId") Long userId);
    
    /**
     * 查找项目中特定类型的待办
     */
    @Query("SELECT t FROM Todo t WHERE t.projectId = :projectId AND t.todoType = :todoType ORDER BY t.createTime ASC")
    List<Todo> findByProjectIdAndTodoType(@Param("projectId") Long projectId, @Param("todoType") TodoType todoType);
}